package top.went.db.dao;

import org.hibernate.annotations.ColumnTransformer;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.web.bind.annotation.GetMapping;
import top.went.pojo.OrderEntity;
import top.went.pojo.UserEntity;

import java.util.List;
import java.util.Map;

/**
 * 订单dao
 */
public interface OrderDao extends CrudRepository<OrderEntity,Integer> {
    /**
     * 订单加载
     * @param delete
     * @param id
     * @return
     */
    @Query("from OrderEntity o where o.loginDelete = ?1 and o.orderId =?2")
    OrderEntity findOne(boolean delete, Integer id);

    /**
     * 通过客户加载订单
     * @param id
     * @return
     */
    @Query("from OrderEntity o where o.loginDelete = false and o.tbCustomerByCusId.cusId = ?1")
    List<OrderEntity> findByCus(Integer id);

    /**
     * 统计合同/订单分布
     * @return
     */
    @Query(nativeQuery = true,value = "select DECODE(ORDER_TYPE,0,'合同',1,'订单','其他') TYPE, count(ORDER_TYPE) COUNT" +
            " from TB_ORDER o where o.LOGIN_DELETE = 0 group by o.ORDER_TYPE")
    List<Object[]> statisticsOrderType();
    /**
     * 统计合同/订单状态
     * @return
     */
    @Query(nativeQuery = true,value = "select DECODE(ORDER_STATUS,0,'执行中',1,'意外结束',2,'结束','其他') TYPE, " +
            "count(ORDER_STATUS) COUNT" +
            " from TB_ORDER o where o.LOGIN_DELETE = 0 group by o.ORDER_STATUS")
    List<Object[]> statisticsOrderStatus();
    /**
     * 统计合同/订单所有者金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select USER_NAME TYPE, sum(nvl(o.TOTAL,0)) count from ORDER_VIEW o " +
            "group by o.USER_ID,USER_NAME")
    List<Object[]> statisticsOrderUserMOney();
    /**
     * 统计合同/订单类型分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(o.ORDER_CATEGORY,'其他') TYPE, count(nvl(o.ORDER_CATEGORY,'其他')) COUNT" +
            " from TB_ORDER o where o.LOGIN_DELETE = 0 group by o.ORDER_CATEGORY")
    List<Object[]> statisticsOrderCategory();
    /**
     * 统计合同/订单类型金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(o.ORDER_CATEGORY,'其他') TYPE, sum(nvl(o.TOTAL,0)) count from ORDER_VIEW o " +
            "group by o.ORDER_CATEGORY")
    List<Object[]> statisticsOrderCategoryMoney();
    /**
     * 统计合同/订单所有者预计毛利分布
     * @return
     */
    @Query(nativeQuery = true,value = "select USER_NAME TYPE, sum(nvl(o.FORECASTMAORI,0)) count from ORDER_VIEW o " +
            "group by o.USER_ID,USER_NAME")
    List<Object[]> statisticsOrderUserfmori();
    /**
     * 统计合同/订单所有者毛利分布
     * @return
     */
    @Query(nativeQuery = true,value = "select USER_NAME TYPE, sum(nvl(o.maori,0)) count from ORDER_VIEW o " +
            "group by o.USER_ID,USER_NAME")
    List<Object[]> statisticsOrderUsermori();

    /**
     * 统计合同/订单所有者回款分布
     * @return
     */
    @Query(nativeQuery = true,value = "select USER_NAME TYPE, sum(nvl(o.RETURNMONEY,0)) count from ORDER_VIEW o " +
            "group by o.USER_ID,USER_NAME")
    List<Object[]> statisticsOrderUserreturn();

    @Query("select count(*) from OrderEntity o where o.loginDelete = false and o.tbUserByUserId.userId = ?1")
    public long countAllByTbUserByUserId(Long userEntity);

    @Query(nativeQuery = true, value = "select count(*) from TB_order where to_number(to_char(ORDER_NEW_DATE,'yyyy'))=?1 and to_number(to_char(ORDER_DATE,'MM')) =?2 and LOGIN_DELETE = 0 ")
    public long countAllByMonth(int year, int month);
    /**
     * 统计所有者合同/订单分布
     * @return
     */
    @Query(nativeQuery = true,value = "select USER_NAME TYPE, count(0) count from ORDER_VIEW o " +
            "group by o.USER_NAME")
    List<Object[]> statisticsUser();

    /**
     * 统计合同/订单时间分布(最大20个)
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM  " +
            "(  " +
            "SELECT A.*, ROWNUM RN  " +
            "FROM " +
            "(select to_char(o.ORDER_NEW_DATE,'yyyy-MM') TYPE, count(0) count from ORDER_VIEW o where o.ORDER_NEW_DATE is not null group by to_char(o.ORDER_NEW_DATE,'yyyy-MM') order by to_char(o.ORDER_NEW_DATE,'yyyy-MM') desc) A  " +
            "WHERE ROWNUM <= 20  " +
            ") " +
            "WHERE RN >= 0")
    List<Object[]> statisticsOrderTime();
    /**
     * 统计合同/订单金额时间分布(最大20个)
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM  " +
            "(  " +
            "SELECT A.*, ROWNUM RN  " +
            "FROM " +
            "(select to_char(o.ORDER_NEW_DATE,'yyyy-MM') TYPE, sum(nvl(o.TOTAL,0)) count from ORDER_VIEW o where o.ORDER_NEW_DATE is not null group by to_char(o.ORDER_NEW_DATE,'yyyy-MM') order by to_char(o.ORDER_NEW_DATE,'yyyy-MM') desc) A  " +
            "WHERE ROWNUM <= 20  " +
            ") " +
            "WHERE RN >= 0")
    List<Object[]> statisticsOrderTimeMoney();
}
